package com.microframework.boot.datarule.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.microframework.base.core.datasource.generator.ColumnMeta;
import com.microframework.base.core.datasource.generator.MetaBuilder;
import com.microframework.base.core.datasource.generator.TableMeta;
import com.microframework.base.core.dbconfig.DbConfigUtil;
import com.microframework.base.core.exception.DataRuleException;
import com.microframework.base.core.exception.DataRuleSqlException;
import com.microframework.base.core.util.UuidUtil;
import com.microframework.base.core.util.sql.SqlParserUtil;
import com.microframework.base.core.util.sql.SqlParserUtil.TableInfo;
import com.microframework.base.web.page.PageQuery;
import com.microframework.boot.datarule.model.domain.Rule;
import com.microframework.boot.datarule.model.domain.RuleCol;
import com.microframework.boot.datarule.model.domain.RuleSql;
import com.microframework.boot.datarule.model.response.RuleDTO;
import com.microframework.boot.datarule.model.response.TableMetaDTO;
import com.microframework.boot.datarule.service.IRuleService;
import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class RuleServiceImpl implements IRuleService {

	
	@Override
	public List<Rule> loadAll(PageQuery<Rule> pageQuery) {
		try {
			String select = "select * ";
			StringBuilder sqlBuilder = new StringBuilder(select + " FROM " + Rule.TABLE_NAME + " WHERE 1=1");
			List<Object> params = new ArrayList<>();

			Rule query = pageQuery.getQuery();
			if (query != null) {
				// 构建查询条件

				if (StringUtils.hasText(query.getName())) {
					sqlBuilder.append(" AND name LIKE ?");
					params.add("%" + query.getName() + "%");
				}

				if (StringUtils.hasText(query.getCode())) {
					sqlBuilder.append(" AND code LIKE ?");
					params.add("%" + query.getCode() + "%");
				}

				if (StringUtils.hasText(query.getParentId())) {
					sqlBuilder.append(" AND parent_id = ?");
					params.add(query.getParentId());
				}

				if (StringUtils.hasText(query.getIsDel())) {
					sqlBuilder.append(" AND is_del = ?");
					params.add(query.getIsDel());
				} else {
					sqlBuilder.append(" AND is_del = ?");
					params.add("0");
				}
			}else {
				sqlBuilder.append(" AND is_del = ?");
				params.add("0");
			}

			// 设置排序
			String orderBy = StringUtils.hasText(pageQuery.getOrderBy()) ? pageQuery.getOrderBy() : " sort";
			String orderDirection = StringUtils.hasText(pageQuery.getOrderDirection()) ? pageQuery.getOrderDirection()
					: " asc ";
			sqlBuilder.append(" ORDER BY " + orderBy + " " + orderDirection);
			
			System.out.println(sqlBuilder.toString());
			List<Rule> rules = Rule.dao.find(sqlBuilder.toString(), params.toArray());
			return buildTree(rules);
		} catch (Exception e) {
			log.error("查询异常{}", e.getMessage());
			return new LinkedList<Rule>();
		}
	}

	public List<Rule> buildTree(List<Rule> nodes) {
		// 1. 创建快速查找映射表
		Map<String, Rule> nodeMap = new HashMap<>();
		Map<String, List<Rule>> parentChildMap = new HashMap<>();

		// 2. 构建索引
		for (Rule node : nodes) {
			String nodeId = node.getId();
			String parentId = node.getParentId();

			// 存储节点映射
			nodeMap.put(nodeId, node);

			// 构建父子关系映射
			parentChildMap.computeIfAbsent(parentId, k -> new ArrayList<>()).add(node);
		}

		// 3. 识别根节点（没有父节点或父节点不存在）
		List<Rule> roots = new ArrayList<>();
		for (Rule node : nodes) {
			String parentId = node.getParentId();

			// 根节点条件：父节点为空、不存在或指向自身（根据业务需求调整）
			if (parentId == null || !nodeMap.containsKey(parentId) || parentId.equals(node.getId())) {
				roots.add(node);
			}
		}

		// 4. 递归构建树形结构
		roots.forEach(root -> buildTreeRecursive(root, parentChildMap));

		return roots;
	}

	private void buildTreeRecursive(Rule node, Map<String, List<Rule>> parentChildMap) {
		String nodeId = node.getId();
		List<Rule> children = parentChildMap.get(nodeId);

		if (children != null && !children.isEmpty()) {
			// 设置子节点并继续递归构建
			node.setChildren(children);
			children.forEach(child -> buildTreeRecursive(child, parentChildMap));
		}
	}

	@Override
	public TableMetaDTO executeSql(String sqlid) throws Exception {
		RuleSql ruleSql=RuleSql.dao.findById(sqlid);
		if(ruleSql==null) {
			throw new DataRuleSqlException("未查询到需要执行的Sql信息.");
		}
		
		if(StrKit.isBlank(ruleSql.getSqlContent())) {
			throw new DataRuleSqlException("请先配置Sql语句.");
		}
		
		String _sql=SqlParserUtil.getSqlBeforeWhere(ruleSql.getSqlContent());
		Map<String, TableInfo> tableMap=SqlParserUtil.parseTables(_sql);
		if(tableMap!=null && tableMap.size()>0) {
			String[] tableNames=tableMap.values().stream()
	        .map(TableInfo::getTableName)
	        .distinct()
	        .toArray(String[]::new);
			
			if(tableNames.length>0) {
				MetaBuilder metaBuilder = new MetaBuilder(Db.use(ruleSql.getDsId()).getConfig().getDataSource());
				metaBuilder.addWhitelist(tableNames);
				metaBuilder.setTableNamePrefixes("");
				metaBuilder.setGenerateRemarks(true);
				metaBuilder.setRemovedTableNamePrefixes("sys_");
				List<TableMeta> tableMetas = metaBuilder.build();
				List<com.jfinal.plugin.activerecord.Record> records=new LinkedList<>();
				records.add(Db.use(ruleSql.getDsId()).findFirst(_sql+" where 1=1"));
				return new TableMetaDTO(tableMetas,records);
			}
		}
		return null;
		
	}

	@Override
	public String generateFields(String id) {
		RuleSql ruleSql=RuleSql.dao.findById(id);
		if(ruleSql==null) {
			throw new DataRuleSqlException("未查询到需要执行的Sql信息.");
		}
		
		if(StrKit.isBlank(ruleSql.getSqlContent())) {
			throw new DataRuleSqlException("请先配置Sql语句.");
		}
		
		String _sql=SqlParserUtil.getSqlBeforeWhere(ruleSql.getSqlContent());
		Map<String, TableInfo> tableMap=SqlParserUtil.parseTables(_sql);
		if(tableMap!=null && tableMap.size()>0) {
			String[] tableNames=tableMap.values().stream()
	        .map(TableInfo::getTableName)
	        .distinct()
	        .toArray(String[]::new);
			
			if(tableNames.length>0) {
				MetaBuilder metaBuilder = new MetaBuilder(Db.use(ruleSql.getDsId()).getConfig().getDataSource());
				metaBuilder.addWhitelist(tableNames);
				metaBuilder.setTableNamePrefixes("");
				metaBuilder.setGenerateRemarks(true);
				metaBuilder.setRemovedTableNamePrefixes("sys_");
				List<TableMeta> tableMetas = metaBuilder.build();
				
				RuleCol ruleCol;
				int sort=0;
				for(TableMeta tableMeta:tableMetas) {
					for(ColumnMeta columnMeta:tableMeta.columnMetas) {
						ruleCol=RuleCol.dao.findFirst("select id from "+RuleCol.TABLE_NAME+" where rule_id=? and table_name=? and field_name=?",id,tableMeta.name,columnMeta.name); 
						if(ruleCol==null) {
							ruleCol=new RuleCol();
							ruleCol.setId(UuidUtil.getUUID32());
							ruleCol.setRuleId(id);
							ruleCol.setSort(sort);
							ruleCol.setName(columnMeta.attrName);
							ruleCol.setLabel(columnMeta.remarks);
							ruleCol.setDataType(org.apache.commons.lang3.StringUtils.substringAfterLast(columnMeta.javaType, "."));
							ruleCol.setTableName(tableMeta.name);
							ruleCol.setFieldRemark(columnMeta.remarks);
							ruleCol.setFieldName(columnMeta.name);
							ruleCol.save();
						}else {
							ruleCol.setRuleId(id);
							ruleCol.setName(columnMeta.attrName);
							if(StrKit.isBlank(ruleCol.getLabel())) {
								ruleCol.setLabel(columnMeta.remarks);
							}
							ruleCol.setDataType(org.apache.commons.lang3.StringUtils.substringAfterLast(columnMeta.javaType, "."));
							ruleCol.setTableName(tableMeta.name);
							ruleCol.setFieldName(columnMeta.name);
							ruleCol.setFieldRemark(columnMeta.remarks);
							ruleCol.update();
						}
						sort++;
					}
				}
			}
		}
		return null;
	}

	@Override
	public Page<RuleCol> pages(PageQuery<RuleCol> pageQuery) {
		try {
			String select="select * ";
			StringBuilder sqlBuilder = new StringBuilder("FROM " + RuleCol.TABLE_NAME + " WHERE 1=1 ");
			List<Object> params = new ArrayList<>();
			RuleCol query = pageQuery.getQuery();
			if (query != null) {
				if (StringUtils.hasText(query.getRuleId())) {
					sqlBuilder.append(" AND rule_id = ?");
					params.add(query.getRuleId());
				}
				
				if (StringUtils.hasText(query.getName())) {
					sqlBuilder.append(" AND name LIKE ?");
					params.add("%" + query.getName() + "%");
				}
				
				if (StringUtils.hasText(query.getLabel())) {
					sqlBuilder.append(" AND label LIKE ?");
					params.add("%" + query.getLabel() + "%");
				}
				
				if (StringUtils.hasText(query.getFieldName())) {
					sqlBuilder.append(" AND field_name LIKE ?");
					params.add("%" + query.getFieldName() + "%");
				}
				
				if (StringUtils.hasText(query.getTableName())) {
					sqlBuilder.append(" AND table_name LIKE ?");
					params.add("%" + query.getTableName() + "%");
				}
			}
			
			// 设置排序
			String orderBy = StringUtils.hasText(pageQuery.getOrderBy()) ? pageQuery.getOrderBy() : " sort";
			String orderDirection = StringUtils.hasText(pageQuery.getOrderDirection()) ? pageQuery.getOrderDirection()
					: " asc ";
			sqlBuilder.append(" ORDER BY " + orderBy + " " + orderDirection);
			return RuleCol.dao.paginate(pageQuery.getPageNumber(), pageQuery.getPageSize(), select,sqlBuilder.toString(), params.toArray());
		} catch (Exception e) {
			log.error("查询列表异常{}", e.getMessage());
			return new Page<RuleCol>(new ArrayList<>(), 1, 10, 0, 0);
		}
	}

	@Override
	public RuleDTO getColumnsByRuleCode(String ruleCode) {
		Rule rule=Rule.dao.findFirst("select * from "+Rule.TABLE_NAME+" where code=? ",ruleCode);
		if(rule==null) {
			throw new DataRuleException(String.format("【%s】对应的数据规则不存在.", ruleCode));
		}
		List<RuleCol> list =RuleCol.dao.find("select * from "+RuleCol.TABLE_NAME+" where rule_id=? order by sort asc ",rule.getId());
		return new RuleDTO(rule,list);
	}


	@Override
	public String executeSaveByRuleCode(String ruleCode, JSONObject params) {
	    // 1. 获取规则基本信息
	    Rule rule = validateRule(ruleCode);
	    if (rule == null) {
	        throw new DataRuleException("规则不存在: " + ruleCode);
	    }

	    RuleSql ruleSql = RuleSql.dao.findById(rule.getId());
	    if (ruleSql == null) {
	        throw new DataRuleException(String.format("【%s】对应的数据来源配置不存在", rule.getName()));
	    }

	    // 2. 检查数据源是否可用
	    if (!DbConfigUtil.isActive(ruleSql.getDsId())) {
	        throw new DataRuleException(String.format("【%s】应用的数据源未启动", rule.getName()));
	    }

	    // 3. 获取规则关联的所有字段配置
	    List<RuleCol> ruleCols = RuleCol.dao.find("select * from " + RuleCol.TABLE_NAME + " where rule_id=?", rule.getId());
	    if (ruleCols == null || ruleCols.isEmpty()) {
	        throw new DataRuleException("规则未配置字段: " + ruleCode);
	    }

	    // 4. 按表名分组字段配置
	    Map<String, List<RuleCol>> tableColumnsMap = ruleCols.stream()
	            .collect(Collectors.groupingBy(RuleCol::getTableName));

	    // 5. 使用 JFinal 事务管理（确保在同一个数据源上执行）
	    try {
	        Db.use(ruleSql.getDsId()).tx(() -> {
	            // 6. 遍历每个表，构建并执行插入/更新操作
	            for (Map.Entry<String, List<RuleCol>> entry : tableColumnsMap.entrySet()) {
	                String tableName = entry.getKey();
	                List<RuleCol> columns = entry.getValue();

	                // 6.1 构建记录对象
	                Record record = new Record();
	                for (RuleCol col : columns) {
	                    String fieldName = col.getFieldName();
	                    JSONObject tableParams = params.getJSONObject(tableName);
	                    if (tableParams != null && tableParams.containsKey(col.getName())) {
	                        Object value = tableParams.get(col.getName());
	                        if (value != null) {
	                            record.set(fieldName, value);
	                        }
	                    }

	                    // 处理必填字段验证
	                    if ( StrKit.equals("1", col.getIsNullable()) && record.get(fieldName) == null) {
	                        throw new DataRuleException(String.format("字段【%s】为必填字段", col.getName()));
	                    }
	                }

	                // 6.2 执行保存操作
	                if (!record.getColumns().isEmpty()) {
	                    // 获取主键字段
	                    String primaryKey = columns.stream()
	                            .filter(RuleCol::getIsPrimaryKey)
	                            .map(RuleCol::getFieldName)
	                            .findFirst()
	                            .orElse(null);

	                    // 处理主键值
	                    if (primaryKey != null) {
	                        Object pkValue = record.get(primaryKey);
	                        if (pkValue == null || StrKit.isBlank(pkValue.toString())) {
	                            record.set(primaryKey, UuidUtil.getUUID()); // 生成 UUID 主键
	                        }
	                    }

	                    try {
	                        if (primaryKey != null && record.get(primaryKey) != null) {
	                            Db.use(ruleSql.getDsId()).update(tableName, primaryKey, record);
	                        } else {
	                        	Db.use(ruleSql.getDsId()).save(tableName, record);
	                        }
	                    } catch (Exception e) {
	                        throw new DataRuleException(String.format("保存表【%s】数据失败: %s", tableName, e.getMessage()), e);
	                    }
	                }
	            }
	            return true; // 返回 true 表示事务提交
	        });
	        return "保存成功";
	    } catch (Exception e) {
	        throw new DataRuleException("数据保存失败: " + e.getMessage(), e);
	    }
	}

	@Override
    public JSONObject executeQueryByRuleCode(String ruleCode, JSONObject params) {
        // 1. 基础校验
        Rule rule = validateRule(ruleCode);
        RuleSql ruleSql = validateRuleSql(rule);
        validateDataSource(ruleSql);

        // 2. 获取SQL模板
        String sqlTemplate = ruleSql.getSqlContent();
        if (StrKit.isBlank(sqlTemplate)) {
            throw new DataRuleException("SQL模板不能为空");
        }

        // 3. 动态构建SQL
        DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(sqlTemplate, params);
        String finalSql = sqlBuilder.build();
        Map<String, Object> paramMap = sqlBuilder.getParamMap();

        // 4. 执行查询
        try {
            SqlPara sqlPara = new SqlPara()
                .setSql(finalSql)
                .addPara(paramMap);
            
            List<Record> records = Db.use(ruleSql.getDsId()).find(sqlPara);

            // 5. 构建返回结果
            return buildSuccessResult(records);
        } catch (Exception e) {
            throw new DataRuleException("查询执行失败: " + e.getMessage(), e);
        }
    }

	@Override
	public String executeDeleteByRuleCode(String ruleCode, JSONObject params) {
	    // 1. 基础校验
	    Rule rule = validateRule(ruleCode);
	    RuleSql ruleSql = validateRuleSql(rule);
	    validateDataSource(ruleSql);

	    // 2. 获取SQL模板（配置示例：DELETE FROM table /*[condition]*/ WHERE id=:id /*[/condition]*/）
	    String sqlTemplate = ruleSql.getSqlContent();
	    if (StrKit.isBlank(sqlTemplate)) {
	        throw new DataRuleException("删除SQL模板不能为空");
	    }

	    // 3. 动态构建SQL
	    DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(sqlTemplate, params);
	    String finalSql = sqlBuilder.build();
	    Map<String, Object> paramMap = sqlBuilder.getParamMap();

	    // 4. 执行删除（带事务）
	    try {
	        Db.use(ruleSql.getDsId()).tx(() -> {
	            SqlPara sqlPara = new SqlPara()
	                .setSql(finalSql)
	                .addPara(paramMap);
	            
	            int affectedRows = Db.use(ruleSql.getDsId()).update(sqlPara);
	            
	            // 安全保护：禁止无条件的全表删除
	            if (!sqlBuilder.hasCondition() && affectedRows > 10) {
	                throw new DataRuleException("拒绝执行无条件的批量删除操作");
	            }
	            
	            return true;
	        });
	        return "删除成功.";
	    } catch (Exception e) {
	        throw new DataRuleException("删除操作失败: " + e.getMessage(), e);
	    }
	}
	@Override
	public JSONObject executePageByRuleCode(String ruleCode, JSONObject params) {
	    // 1. 基础校验
	    Rule rule = validateRule(ruleCode);
	    RuleSql ruleSql = validateRuleSql(rule);
	    validateDataSource(ruleSql);

	    // 2. 获取分页参数（带默认值）
	    int pageNumber = params.getIntValue("pageNumber", 1);
	    int pageSize = params.getIntValue("pageSize", 10);
	    params.put("offset", (pageNumber - 1) * pageSize);
	    params.put("limit", pageSize);

	    // 3. 获取SQL模板（要求包含 /*[paginate]*/ 标记）
	    String sqlTemplate = ruleSql.getSqlContent();
	    if (StrKit.isBlank(sqlTemplate)) {
	        throw new DataRuleException("SQL模板不能为空");
	    }

	    // 4. 构建动态SQL（分页部分固定使用LIMIT语法）
	    DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(sqlTemplate, params) {
	        @Override
	        public String build() {
	            String originalSql = super.build();
	            // 确保SQL包含分页标记
	            if (!originalSql.contains("/*[paginate]*/")) {
	                throw new DataRuleException("分页查询必须包含/*[paginate]*/标记");
	            }
	            // 替换为数据库特定的分页语法
	            return originalSql.replace("/*[paginate]*/", "LIMIT :limit OFFSET :offset");
	        }
	    };

	    String finalSql = sqlBuilder.build();
	    Map<String, Object> paramMap = sqlBuilder.getParamMap();

	    // 5. 执行分页查询
	    try {
	        // 5.1 查询数据
	        SqlPara dataSqlPara = new SqlPara().setSql(finalSql).addPara(paramMap);
	        List<Record> records = Db.use(ruleSql.getDsId()).find(dataSqlPara);

	        // 5.2 查询总数（自动移除分页和ORDER BY子句）
	        String countSql = buildCountSql(finalSql);
	        SqlPara countSqlPara = new SqlPara().setSql(countSql).addPara(paramMap);
	        int totalCount = Db.use(ruleSql.getDsId()).queryInt(countSqlPara.getSql(), countSqlPara.getPara());

	        // 6. 构建返回结果
	        return buildPageResult(records, pageNumber, pageSize, totalCount);
	    } catch (Exception e) {
	        throw new DataRuleException("分页查询失败: " + e.getMessage(), e);
	    }
	}

	

    //=== 辅助方法 ===//
    private Rule validateRule(String ruleCode) {
        Rule rule = Rule.dao.findFirst("select * from sys_rule where code=?", ruleCode);
        if (rule == null) throw new DataRuleException("规则不存在: " + ruleCode);
        return rule;
    }

    private RuleSql validateRuleSql(Rule rule) {
        RuleSql ruleSql = RuleSql.dao.findById(rule.getId());
        if (ruleSql == null) throw new DataRuleException("未配置SQL规则");
        return ruleSql;
    }

    private void validateDataSource(RuleSql ruleSql) {
        if (!DbConfigUtil.isActive(ruleSql.getDsId())) {
            throw new DataRuleException("数据源未启用");
        }
    }

    private JSONObject buildSuccessResult(List<Record> records) {
        JSONObject result = new JSONObject();
        JSONArray data = new JSONArray();
        
        for (Record record : records) {
            JSONObject item = new JSONObject();
            record.getColumns().forEach((k, v) -> {
                // 处理 user_name -> {user: {name: value}} 结构
                String[] parts = k.split("_", 2);
                if (parts.length == 2) {
                    // 修正点：显式转换为JSONObject
                    ((JSONObject)item.computeIfAbsent(parts[0], x -> new JSONObject()))
                        .put(parts[1], v);
                } else {
                    item.put(k, v);
                }
            });
            data.add(item);
        }
        
        result.put("code", 200);
        result.put("msg", "success");
        result.put("data", data);
        return result;
    }
    private String buildCountSql(String originalSql) {
        // 1. 移除分页部分
        String sql = originalSql.replaceAll("LIMIT\\s+:limit\\s+OFFSET\\s+:offset", "");
        
        // 2. 移除ORDER BY子句（不影响计数）
        sql = sql.replaceAll("ORDER\\s+BY\\s+[^\\s]+(\\s+(ASC|DESC))?", "");
        
        // 3. 转换为COUNT查询
        return "SELECT COUNT(*) FROM (" + sql + ") temp_count_table";
    }

    private JSONObject buildPageResult(List<Record> records, int pageNumber, int pageSize, int totalCount) {
        JSONObject result = new JSONObject();
        JSONArray data = new JSONArray();
        
        // 数据转换逻辑保持不变...
        for (Record record : records) {
            JSONObject item = new JSONObject();
            record.getColumns().forEach((k, v) -> {
                String[] parts = k.split("_", 2);
                if (parts.length == 2) {
                    ((JSONObject)item.computeIfAbsent(parts[0], x -> new JSONObject()))
                        .put(parts[1], v);
                } else {
                    item.put(k, v);
                }
            });
            data.add(item);
        }

        // 修正点：分步构建page对象
        JSONObject pageInfo = new JSONObject();
        pageInfo.put("pageNumber", pageNumber);
        pageInfo.put("pageSize", pageSize);
        pageInfo.put("totalCount", totalCount);
        pageInfo.put("totalPage", (int) Math.ceil((double)totalCount / pageSize));

        result.put("code", 200);
        result.put("msg", "success");
        result.put("data", data);
        result.put("page", pageInfo); // 最终放入分页信息
        
        return result;
    }

//    /**
//     * SQL模板构建工具
//     * @param sqlTemplate SQL模板，示例：
//     *                   SELECT * FROM user
//     *                   /*[name]*/ WHERE name=:name /*[/name]*/
//     *                   /*[age|18]*/ AND age>:age /*[/age]*/ 
//     * @param params      参数映射表，支持：
//     *                   - 直接参数 :param
//     *                   - 表字段参数 :table.field
//     *                   - 默认值语法 /*[param|default]*/
//     */
    private static class DynamicSqlBuilder {
        private static final Pattern DYNAMIC_TAG_PATTERN = Pattern.compile("/\\*\\[(\\w+)(?:\\|(.*?))?\\]\\*/");
        private static final Pattern NAMED_PARAM_PATTERN = Pattern.compile(":(\\w+(?:\\.\\w+)?)");
        
        private final String sqlTemplate;
        private final JSONObject params;
        private final Map<String, Object> paramMap = new HashMap<>();
        private boolean hasCondition = false;
        private boolean hasWhereClause = false;

        public DynamicSqlBuilder(String sqlTemplate, JSONObject params) {
            this.sqlTemplate = sqlTemplate;
            this.params = params != null ? params : new JSONObject();
        }

        public String build() {
            StringBuilder finalSql = new StringBuilder();
            int pos = 0;
            Matcher matcher = DYNAMIC_TAG_PATTERN.matcher(sqlTemplate);

            while (matcher.find()) {
                // 添加静态部分
                finalSql.append(sqlTemplate.substring(pos, matcher.start()));
                pos = matcher.end();

                String tagName = matcher.group(1);
                String defaultValue = matcher.group(2);

                // 查找结束标记
                String endTag = "/*[/" + tagName + "]*/";
                int endPos = sqlTemplate.indexOf(endTag, pos);
                if (endPos == -1) continue;

                // 检查参数是否有效
                if (hasValidParam(tagName, defaultValue)) {
                    String dynamicPart = sqlTemplate.substring(pos, endPos);
                    
                    // 检测条件类型标签
                    if (isConditionTag(tagName)) {
                        this.hasCondition = true;
                        // 检测是否是首个WHERE条件
                        if (!hasWhereClause && dynamicPart.trim().toUpperCase().startsWith("WHERE")) {
                            this.hasWhereClause = true;
                        }
                    }
                    
                    finalSql.append(dynamicPart);
                    extractNamedParams(dynamicPart);
                }
                pos = endPos + endTag.length();
            }
            finalSql.append(sqlTemplate.substring(pos));
            return finalSql.toString();
        }

        private boolean isConditionTag(String tagName) {
            // 排除字段选择类标签
            return !tagName.toLowerCase().matches("select|fields?|columns?|orderby|groupby");
        }

        private boolean hasValidParam(String paramName, String defaultValue) {
            Object value = getParamValue(paramName);
            if (value != null && !"".equals(value)) return true;
            
            // 处理默认值
            if (defaultValue != null) {
                paramMap.put(paramName, defaultValue);
                return true;
            }
            return false;
        }

        private Object getParamValue(String paramName) {
            // 1. 尝试直接获取
            if (params.containsKey(paramName)) {
                Object val = params.get(paramName);
                paramMap.put(paramName, val);
                return val;
            }

            // 2. 尝试获取 table.field
            String[] parts = paramName.split("\\.");
            if (parts.length == 2) {
                JSONObject tableParams = params.getJSONObject(parts[0]);
                if (tableParams != null && tableParams.containsKey(parts[1])) {
                    Object val = tableParams.get(parts[1]);
                    paramMap.put(paramName, val);
                    return val;
                }
            }
            return null;
        }

        private void extractNamedParams(String sqlFragment) {
            Matcher matcher = NAMED_PARAM_PATTERN.matcher(sqlFragment);
            while (matcher.find()) {
                String paramName = matcher.group(1);
                getParamValue(paramName); // 确保参数被收集
            }
        }

        /**
         * 是否包含有效条件（WHERE/AND/OR等）
         */
        public boolean hasCondition() {
            return this.hasCondition;
        }

        /**
         * 是否包含完整的WHERE子句
         */
//        public boolean hasWhereClause() {
//            return this.hasWhereClause;
//        }

        public Map<String, Object> getParamMap() {
            return paramMap;
        }
    }

}
